using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Newtonsoft.Json;
using SugarFineUI.Enties;
using SugarFineUI.DBServices.Base ;
using SqlSugar;
using SugarFineUI.Framework.Log;
using DbType = SqlSugar.DbType;
//参考sqlsugar 官方文档：http://www.codeisbug.com/Doc/8/
namespace SugarFineUI.DBServices
{
    public class DB_Base
    {
        /// <summary>
        /// 获取数据库连接实体，禁止使用静态变量对接
        /// </summary>
        public static SqlSugarClient Instance
        {
            get
            {
                SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = ConfigurationManager.AppSettings["ConnectionString"],
                    DbType = DbType.SqlServer,//设置数据库类型
                    IsAutoCloseConnection = true,//自动释放数据务，如果存在事务，在事务结束后释放
                    InitKeyType = InitKeyType.SystemTable,//从数据库系统表读取主键信息中（InitKeyType.Attribute从实体特性中读取主键自增列信息）
                    ConfigureExternalServices = new ConfigureExternalServices()
                    {
                        DataInfoCacheService = new HttpRuntimeCache() //设置缓存，继承ICacheService自已实现的一个类
                    }
                });

                //http://www.codeisbug.com/Doc/8/1140  调试SQL，AOP ,日志
                db.Aop.OnLogExecuted = (sql, pars) => //SQL执行完事件
                {
#if DEBUG
                    //获取执行后的总毫秒数
                    double sqlExecutionTotalMilliseconds = db.Ado.SqlExecutionTime.TotalMilliseconds;
#endif
                };
                
                db.Aop.OnLogExecuting = (sql, pars) => //SQL执行前事件。可在这里查看生成的sql
                {
#if DEBUG
                    string tempSQL = LookSQL(sql, pars);
#endif
                };
                db.Aop.OnError = (exp) =>//执行SQL 错误事件
                {
                    //exp.sql exp.parameters 可以拿到参数和错误Sql  
                    StringBuilder sb_SugarParameterStr = new StringBuilder("###SugarParameter参数为:");
                    var parametres = exp.Parametres as SugarParameter[];
                    if (parametres != null)
                    {
                        sb_SugarParameterStr.Append(JsonConvert.SerializeObject(parametres));
                    }

                    StringBuilder sb_error = new StringBuilder();
                    sb_error.AppendLine("SqlSugarClient执行sql异常信息:" + exp.Message);
                    sb_error.AppendLine("###赋值后sql:" + LookSQL(exp.Sql, parametres));
                    sb_error.AppendLine("###带参数的sql:" + exp.Sql);
                    sb_error.AppendLine("###参数信息:" + sb_SugarParameterStr.ToString());

                    Logger.Default.Error(sb_error.ToString());
                };

                //db.Aop.OnExecutingChangeSql = (sql, pars) => //SQL执行前 可以修改SQL
                //{
                //    return new KeyValuePair<string, SugarParameter[]>(sql, pars);
                //};

                return db;
            }
        }

        /// <summary>
        /// 查看赋值后的sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars">参数</param>
        /// <returns></returns>
        private static string LookSQL(string sql ,SugarParameter[] pars)
        {
            if (pars == null || pars.Length == 0) return sql ;

            StringBuilder sb_sql=new StringBuilder(sql);
            var tempOrderPars = pars.Where(p => p.Value != null).OrderByDescending(p => p.ParameterName.Length).ToList();//防止 @par1错误替换@par12
            for (var index = 0; index < tempOrderPars.Count; index++)
            {
                sb_sql.Replace(tempOrderPars[index].ParameterName, "'" + tempOrderPars[index].Value.ToString() + "'") ;
            }

            return sb_sql.ToString() ;
        }


        #region 获取实体

        public static T GetModel<T>(object id)
        {
            return GetModel<T>(id, DB_Base.Instance);
        }

        public static T GetModel<T>(object id, SqlSugarClient db)
        {
            return db.Queryable<T>().InSingle(id);
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增int类型id,实体需要设置主键特性(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int InsertModel<T>(T t) where T : class, new()
        {
            return InsertModel(t, DB_Base.Instance);
        }

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增int类型id,实体需要设置主键特性)(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int InsertModel<T>(T t, SqlSugarClient db) where T : class, new()
        {
            //Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return db.Insertable(t).
                //IgnoreColumns(p=>p.Equals("id",StringComparison.InvariantCultureIgnoreCase)).
                IgnoreColumns(true, false).ExecuteReturnIdentity();
        }

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增long类型id,实体需要设置主键特性(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static long InsertModel_BigIdentity<T>(T t) where T : class, new()
        {
            return InsertModel_BigIdentity(t, DB_Base.Instance);
        }

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增long类型id,实体需要设置主键特性)(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static long InsertModel_BigIdentity<T>(T t, SqlSugarClient db) where T : class, new()
        {
            //Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return db.Insertable(t).
                //IgnoreColumns(p=>p.Equals("id",StringComparison.InvariantCultureIgnoreCase)).
                IgnoreColumns(true, false).ExecuteReturnBigIdentity();
        }
        #endregion

        #region 修改
        /// <summary>
        /// 根据主键更新实体(为null字段不更新,注意model有默认值的情况)，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int UpdateModelByKey<T>(T t) where T : class, new()
        {
            return UpdateModelByKey(t, DB_Base.Instance);
        }

        /// <summary>
        /// 根据主键更新实体，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModelByKey<T>(T t, SqlSugarClient db) where T : class, new()
        {
            //字段null，不进行更新
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件表达式更新实体(为null字段不更新,注意model有默认值的情况)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="expression">表达式</param>
        /// <returns></returns>
        public static int UpdateModelsIgnoreNull<T>(T t, Expression<Func<T, bool>> expression) where T : class, new()
        {
            return UpdateModelsIgnoreNull(t, expression, DB_Base.Instance);
        }

        /// <summary>
        /// 根据条件表达式更新实体(为null字段不更新,注意model有默认值的情况)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="expression">表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModelsIgnoreNull<T>(T t, Expression<Func<T, bool>> expression, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).Where(expression).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件表达式更新实体(指定要更新的列)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t, Expression<Func<T, object>> columns, Expression<Func<T, bool>> expression) where T : class, new()
        {
            return UpdateModels<T>(t, columns, expression, DB_Base.Instance);
        }

        /// <summary>
        /// 根据条件表达式更新实体(指定要更新的列)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <param name="expression">表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t, Expression<Func<T, object>> columns, Expression<Func<T, bool>> expression, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable(t).UpdateColumns(columns).Where(expression).ExecuteCommand();
        }

        /* 不添加实体的 更新
         db.Updateable<Entity.sysAdmin>()
                    .SetColumns(p => new Entity.sysAdmin { photo = photo , Password =newPwd})
                    .Where(p => p.ID == sm.id && p.Password==oldPwd).ExecuteCommand() > 0;
         */

        #endregion

        #region 删除方法

        /// <summary>
        /// 删除ids集合条件的字符串(高效率写法,注意防止注入攻击)
        /// </summary>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <param name="key">主键字段</param>
        /// <returns></returns>
        public static bool DeleteByWhereSql_ids<T>(string ids, string key = "id") where T : class, new()
        {
            return DeleteByWhereSql_ids<T>(ids, DB_Base.Instance, key);
        }

        /// <summary>
        /// 删除ids集合条件的字符串(高效率写法,注意防止注入攻击)
        /// </summary>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <param name="key">主键字段</param>
        /// <returns></returns>
        public static bool DeleteByWhereSql_ids<T>(string ids, SqlSugarClient db, string key = "id") where T : class, new()
        {
            return db.Deleteable<T>().Where(string.Format(" {0} IN ({1})", key, ids)).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 删除ids集合条件的字符串
        /// </summary>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式 </param>
        /// <returns></returns>
        public static bool DeleteByIds<T>(string ids) where T : class, new()
        {
            return DeleteByIds<T>(ids, DB_Base.Instance);
        }

        /// <summary>
        /// 删除ids集合条件的字符串
        /// </summary>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式 </param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static bool DeleteByIds<T>(string ids, SqlSugarClient db) where T : class, new()
        {
            return db.Deleteable<T>(GetIntListByString(ids)).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 删除ids集合条件的字符串
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <returns></returns>
        public static bool DeleteByInt64_Ids<T>(string ids) where T : class, new()
        {
            return DeleteByInt64_Ids<T>(ids, DB_Base.Instance);
        }

        /// <summary>
        /// 删除ids集合条件的字符串
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static bool DeleteByInt64_Ids<T>(string ids, SqlSugarClient db) where T : class, new()
        {
            return db.Deleteable<T>(GetLongListByString(ids)).ExecuteCommand() > 0;
        }


        /// <summary>
        /// 根据条件表达式删除，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static int DeleteModels<T>( Expression<Func<T, bool>> expression) where T : class, new()
        {
            return DeleteModels<T>(expression, DB_Base.Instance);
        }

        /// <summary>
        /// 根据条件表达式删除，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int DeleteModels<T>(Expression<Func<T, bool>> expression, SqlSugarClient db) where T : class, new()
        {
            return db.Deleteable<T>().Where(expression).ExecuteCommand();
        }

        #endregion

        #region ids转集合

        /// <summary>
        /// 将字符串转成int数组(, 号分割)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static int[] GetIntListByString(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return null;
            return Array.ConvertAll<string, int>(ids.Split(','), Int32.Parse);
        }

        /// <summary>
        /// 将字符串转成long数组(, 号分割)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static long[] GetLongListByString(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return null;
            return Array.ConvertAll<string, long>(ids.Split(','), Int64.Parse);
        }


        public static string GetStringByList(IEnumerable<int> ints)
        {
            if (!ints.Any())
            {
                return string.Empty;
            }

            StringBuilder sb=new StringBuilder();
            foreach (var it in ints)
            {
                sb.Append("," + it);
            }

            return sb.ToString().Substring(1);
        }

        #endregion
    }
}

#region 示例说明

#region 不添加实体的 更新方法

/* //不添加实体的 更新
      db.Updateable<Entity.sysAdmin>()
                 .SetColumns(p => new Entity.sysAdmin { photo = photo , Password =newPwd})
                 .Where(p => p.ID == sm.id && p.Password==oldPwd).ExecuteCommand() > 0;
      */

#endregion

#region 多表连接查询

/*
var employeeList = db.Queryable<Entity.PlanStatus, Entity.Leader, Entity.Employee, Entity.MBOrole>((s, l, e, m) => new object[]
                         {
                            JoinType.Left,s.MBOroleId==l.MBOroleId,
                            JoinType.Left,e.id==l.EmployeeId,
                            JoinType.Left,s.MBOroleId==m.id
                         })
                        .Where((s, l, e, m) => ids.Contains(s.PlanId))
                        .Select((s, l, e, m) => new Models.StatusModel
                        {
                            PlanId = s.PlanId,
                            MBOroleId = s.MBOroleId,
                            MBOroleName = m.MBOroleName,
                            employeeNum = e.employeeNum,
                            employeeName = e.employeeName,
                            Status = s.Status
                        }).ToList();
 */

#endregion

#region 跨数据库多表查询
// 跨数据库多表查询
/*

var sql = db.Queryable<Entity.WorkContent, Entity.Work>((c, w) => new object[]
{
    JoinType.Left,c.PlanId==w.PlanID
})
.AS<Entity.WorkContent>("[bb].[dbo].[WorkContent]")
.AS<Entity.Work>("[aa].[dbo].[Work]")
.Select((c, w) => c)
.ToSql();


*/

// 如果单表连自己。可以复制 Entity.Work为Entity.Work2，这样区分。
/*

var sql = db.Queryable<Entity.Work2, Entity.Work>((c, w) => new object[]
{
    JoinType.Left,c.PlanId==w.PlanID
})
.AS<Entity.Work2>("[bb].[dbo].[Work]")
.AS<Entity.Work>("[aa].[dbo].[Work]")
.Select((c, w) => c)
.ToSql();
*/
#endregion

#region 调用事务的例子

/* // 参考 http://www.codeisbug.com/Doc/8/1135

            var db = DBServices.DB_Base.Instance;
           
            try
            {
                db.Ado.BeginTran();

                //删除                
                db.Deleteable<Entity.PlanContent>(ids).ExecuteCommand();

                //更新                
                db.Updateable(modifiedDict).AS(tableName).WhereColumns(nameof(Entity.PlanContent.id)).ExecuteCommand();
                
                //添加    
                db.Insertable(dicList).AS(tableName).ExecuteCommand();                

                //提交事务
                db.Ado.CommitTran();
            }
            catch (Exception ex)
            {
                //有异常 则回滚
                db.Ado.RollbackTran();              
            }
 */

#endregion

#endregion

